package com.wanmait.spacesys.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

public class DbHelper {
	
	private static String url,className,user,password;
	static {
		ResourceBundle rs = ResourceBundle.getBundle("db");
		user = rs.getString("user");
		password = rs.getString("password");
		className = rs.getString("className");
		url = rs.getString("url");
		try {
			Class.forName(className);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//连接数据库的封装方法
	public static Connection getConnection() {
		try {
			Connection conn = DriverManager.getConnection(url, user,password);
			return conn;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 查询数据的静态封装方法，返回一个list类型
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<Map<String,Object>> select(String sql,Object...params) {
		ArrayList<Map<String,Object>> list = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i=0;params!=null&&i<params.length;i++) {
				ps.setObject(i+1,params[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData md = rs.getMetaData();//元数据(数据库里面的字段名)
			int cols = md.getColumnCount();//结果集列数，cols就是查询出来的列数。
			while(rs.next()) {
				/**
				 * row代表每一行
				 */
				HashMap<String, Object> row = new HashMap<String, Object>();
				for(int i=0;i<cols;i++) {
					//key就是获取的字段名
					//value就是对应字段名下面的数值
					String key = md.getColumnName(i+1);
					Object value = rs.getObject(i+1);
					//把字段名和数值放入HashMap中
					row.put(key, value);
				}
				//再把row放入list中
				list.add(row);
			}
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return list;
	}
	//增删改的封装方法
	public static Integer update(String sql,Object...params) {
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			for(int i=0;params!=null&&i<params.length;i++) {
				ps.setObject(i+1,params[i]);
			}
			ps.executeUpdate();
			//当是insert的时候，才返回这个结果
			rs = ps.getGeneratedKeys();
			if(rs!=null&&rs.next()) {
				int id = rs.getInt(1);
			
				return id;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps, conn);
		}
		return null;
	}
	public static void close(ResultSet rs,Statement ps,Connection conn) {
		close(rs);
		close(ps);
		close(conn);
	}
	public static void close(Statement ps ,Connection conn) {
		close(ps);
		close(conn);
	}
	public static void close(Statement ps) {
		if(ps==null) return;
		try {
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void close(Connection conn) {
		if(conn==null) return;
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void close(ResultSet rs) {
		if(rs==null) return;
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		System.out.println(getConnection());
	}
}
